package com.mclimber.test;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Calendar;
import java.util.Date;

import org.apache.poi.hssf.usermodel.HSSFHeader;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Header;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.WorkbookUtil;

/**
 * Hello world!
 *
 */
public class App2 {
	public static void main(String[] args) throws IOException {
		Workbook wb = new HSSFWorkbook();
		CreationHelper createHelper = wb.getCreationHelper();
		Sheet sheet1 = wb.createSheet("new sheet");

		Header header = sheet1.getHeader();
		header.setCenter("Center Header");
		header.setLeft("Left Header");
		header.setRight(HSSFHeader.font("Stencil-Normal", "Italic") + HSSFHeader.fontSize((short) 16)
				+ "Right w/ Stencil-Normal Italic font and size 16");
		for (int i = 0; i < 10; i++) {
			Row row = sheet1.createRow(i);
			row.createCell(0).setCellValue(i + "行");
			row.createCell(1).setCellValue(false);
			Cell cell2 = row.createCell(2);
			CellStyle style2 = wb.createCellStyle();

			Font font = wb.createFont();
			font.setFontHeightInPoints((short) 12);
			font.setFontName("Courier New");
			font.setItalic(true);
			font.setStrikeout(true);

			style2.setFont(font);

			style2.setBorderBottom(CellStyle.BORDER_THIN);
			style2.setBottomBorderColor(IndexedColors.BLACK.getIndex());
			style2.setBorderLeft(CellStyle.BORDER_THIN);
			style2.setLeftBorderColor(IndexedColors.GREEN.getIndex());
			style2.setBorderRight(CellStyle.BORDER_THIN);
			style2.setRightBorderColor(IndexedColors.BLUE.getIndex());
			style2.setBorderTop(CellStyle.BORDER_MEDIUM_DASHED);
			style2.setTopBorderColor(IndexedColors.RED.getIndex());
			cell2.setCellStyle(style2);

			style2.setDataFormat(createHelper.createDataFormat().getFormat("yyyy-MM-dd HH:mm:ss"));
			cell2.setCellStyle(style2);
			cell2.setCellValue(new Date());
			row.createCell(3).setCellValue(new Double("10"));
			row.createCell(4).setCellValue("<html><body>123</body></html>");
			row.createCell(5).setCellValue("123");
			row.createCell(6).setCellValue(createHelper.createRichTextString("This is a string"));
			row.createCell(7).setCellValue(1.1);
			row.createCell(8).setCellValue(Calendar.getInstance());
		}

		for (Sheet sheet : wb) {
			for (Row row : sheet) {
				for (Cell cell : row) {
					System.out.println("sheet:" + cell.getSheet().getSheetName() + ";row:" + row.getRowNum()
							+ ";cell value:" + cell);
				}
				System.out.println("\n");
			}
		}
		System.out.println("-----------------------------------------------");
		/*int rowStart = Math.min(15, sheet1.getFirstRowNum());
		int rowEnd = Math.max(1400, sheet1.getLastRowNum());
		for (int rowNum = rowStart; rowNum < rowEnd; rowNum++) {
			Row r = sheet1.getRow(rowNum);
			if (r == null) {
				System.out.println("row index " + rowNum + " row is null ! \n");
				continue;
			}
			int lastColumn = Math.max(r.getLastCellNum(), 10);

			for (int cn = 0; cn < lastColumn; cn++) {
				Cell c = r.getCell(cn, Row.RETURN_BLANK_AS_NULL);
				if (c == null) {
					System.out.println("row: " + r + " cell is null ! \n");
				} else {
					System.out.println(
							"sheet:" + c.getSheet().getSheetName() + ";row:" + r.getRowNum() + ";cell value:" + c);
				}
			}
		}*/

		Sheet sheet2 = wb.createSheet("second sheet");
		Row row = sheet2.createRow(2);
		Cell cell = row.createCell(2);
		cell.setCellValue("Use \n with word wrap on to create a new line");

		CellStyle cs = wb.createCellStyle();
		cs.setWrapText(true);
		cell.setCellStyle(cs);
		row.setHeightInPoints((2 * sheet2.getDefaultRowHeightInPoints()));
		sheet2.autoSizeColumn((short) 2);

		String safeName = WorkbookUtil.createSafeSheetName("[O'Brien's sales*?]"); // returns
		Sheet sheet3 = wb.createSheet(safeName);
		FileOutputStream fileOut = new FileOutputStream("D:\\POI\\workbook.xls");
		wb.write(fileOut);
		fileOut.close();
		System.out.println("-----------------end---------------------");
		
		InputStream is = new FileInputStream(new File("C:\\Users\\Administrator\\Desktop\\国航EMD渠道接口管理系统测试报告_0620.xls"));
		Workbook wb1 = new HSSFWorkbook(is);
		Sheet sheet = wb1.getSheetAt(0);
		for (Sheet endSheet : wb1) {
			for (Row endRow : endSheet) {
				for (Cell endCell : endRow) {
					System.out.println("sheet:" + endCell.getSheet().getSheetName() + ";row:" + endRow.getRowNum()
							+ ";cell value:" + endCell);
				}
				System.out.println("\n");
			}
		}
		
		
		

	}
}
